use sqlx::mysql::MySqlPoolOptions;
use sqlx::Error;

use mysql::prelude::*;
use r2d2::Pool;
use r2d2_mysql::MysqlConnectionManager;

async fn establish_connection() -> Result<sqlx::Pool<MySqlPool>, Error> {
    let pool = MySqlPoolOptions::new()
        .connect("mysql://paas:paas@192.168.15.194:3308/ds_org_merchant").await?;

    Ok(pool)
}

async fn insert_data(pool: &sqlx::Pool<MySqlPool>) -> Result<(), Error> {
    sqlx::query("INSERT INTO users (name, age) VALUES (?, ?)")
        .bind("Alice")
        .bind(30)
        .execute(pool)
        .await?;

    Ok(())
}

async fn delete_data(pool: &sqlx::Pool<MySqlPool>) -> Result<(), Error> {
    sqlx::query("DELETE FROM users WHERE id = ?")
        .bind(1)
        .execute(pool)
        .await?;

    Ok(())
}

async fn update_data(pool: &sqlx::Pool<MySqlPool>) -> Result<(), Error> {
    sqlx::query("UPDATE users SET age = ? WHERE id = ?")
        .bind(31)
        .bind(1)
        .execute(pool)
        .await?;

    Ok(())
}

async fn select_data(pool: &sqlx::Pool<MySqlPool>) -> Result<(), Error> {
    let rows = sqlx::query!("SELECT id, name, age FROM users")
        .fetch_all(pool)
        .await?;

    for row in rows {
        println!("ID: {}, Name: {}, Age: {}", row.id, row.name, row.age);
    }

    Ok(())
}

async fn select_page_data(pool: &sqlx::Pool<MySqlPool>) -> Result<(), Error> {


    let conn = pool.get().unwrap();

    // 分页查询参数
    let page: u32 = 1;
    let page_size: u32 = 10;

    // 构建分页查询SQL
    let offset = (page - 1) * page_size;
    let sql_query = format!(
        "SELECT * FROM aaa LIMIT {}, {}",
        offset, page_size
    );

    // 执行查询
    let query_result = conn.query_map(&*sql_query, |row| {
        // 解析每一行，这里假设表中有id和name两个字段
        let id: i32 = row.get("id");
        let name: String = row.get("name");
        (id, name)
    }).unwrap();

    // 打印查询结果
    for row in query_result {
        println!("{:?}: {:?}", row.0, row.1);
    }
}

#[tokio::main]
async fn main() -> Result<(), Error> {
    let pool = establish_connection().await?;
    select_page_data(&pool).await?;
    // let manager = MysqlConnectionManager::new("mysql://username:password@localhost:3306/database");
    // let pool = Pool::new(manager).unwrap();
    // Insert data
    // insert_data(&pool).await?;
    //
    // // Delete data
    // delete_data(&pool).await?;
    //
    // // Update data
    // update_data(&pool).await?;
    //
    // // Select data
    // select_data(&pool).await?;

    Ok(())
}